package com.wendyxie.sproutup.db

import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.util.Log
import com.wendyxie.sproutup.entity.UserEntity
import com.wendyxie.sproutup.utils.RandomUtil

class SQLUserHelper {
    // 定义静态变量
    companion object {
        private const val USER_TABLE_NAME = "user"
        private const val KEY_USER_ID = "user_id"
        private const val KEY_BABY_ID = "baby_id"
        private const val KEY_CURRENT_TOTAL_SCORE = "current_total_score"
        private const val KEY_CURRENT_RECEIVED_SCORE = "current_received_score"
        private const val KEY_SELECTED_TREE_ID = "selected_tree_id"
        private const val KEY_SELECTED_TREE_SKIN_URL = "selected_tree_skin_url"
        private const val KEY_BABY_PWD = "baby_pwd"

        // 创建用户信息表
        fun createUserTable(db: SQLiteDatabase) {
            val createTable = """
                CREATE TABLE $USER_TABLE_NAME (
                    $KEY_USER_ID varchar(36) default '',
                    $KEY_BABY_ID varchar(36) not null primary key,
                    $KEY_CURRENT_TOTAL_SCORE int,
                    $KEY_CURRENT_RECEIVED_SCORE int,
                    $KEY_SELECTED_TREE_ID varchar(36),
                    $KEY_SELECTED_TREE_SKIN_URL varchar(36)
                )
            """.trimIndent()
            db.execSQL(createTable)
        }

        // 表结构变更，新增字段 baby_pwd
        fun addBabyPwdColumn(db: SQLiteDatabase) {
            val alterTable = """
                ALTER TABLE $USER_TABLE_NAME ADD COLUMN $KEY_BABY_PWD varchar(36) default '1234'
            """.trimIndent()
            db.execSQL(alterTable)
        }

        // 添加用户信息
        fun addUser(userEntity: UserEntity, db: SQLiteDatabase) {
            val contentValues = ContentValues().apply {
                if (userEntity.userId.isNotEmpty()) {
                    put(KEY_USER_ID, userEntity.userId)
                }else {
                    put(KEY_USER_ID, RandomUtil.getUUID())
                }
                if (userEntity.babyId.isNotEmpty()) {
                    put(KEY_BABY_ID, userEntity.babyId)
                }else {
                    put(KEY_BABY_ID, RandomUtil.getUUID())
                }
                put(KEY_BABY_ID, userEntity.babyId)
                put(KEY_CURRENT_TOTAL_SCORE, userEntity.currentTotalScore)
                put(KEY_CURRENT_RECEIVED_SCORE, userEntity.currentReceivedScore)
                put(KEY_SELECTED_TREE_ID, userEntity.selectedTreeId)
                put(KEY_SELECTED_TREE_SKIN_URL, userEntity.selectedTreeSkinUrl)
            }
            db.insertOrThrow(USER_TABLE_NAME, null, contentValues)
        }

        // 根据用户ID，修改用户信息
        fun updateUserById(userEntity: UserEntity, db: SQLiteDatabase) {
            val contentValues = ContentValues().apply {
                put(KEY_CURRENT_TOTAL_SCORE, userEntity.currentTotalScore)
                put(KEY_CURRENT_RECEIVED_SCORE, userEntity.currentReceivedScore)
                put(KEY_SELECTED_TREE_ID, userEntity.selectedTreeId)
                put(KEY_SELECTED_TREE_SKIN_URL, userEntity.selectedTreeSkinUrl)
            }
            db.update(USER_TABLE_NAME, contentValues, "$KEY_BABY_ID = ?", arrayOf(userEntity.babyId))
        }

        // 修改用户密码
        fun updateUserPwdById(babyId: String, babyPwd: String, db: SQLiteDatabase) {
            val contentValues = ContentValues().apply {
                put(KEY_BABY_PWD, babyPwd)
            }
            db.update(USER_TABLE_NAME, contentValues, "$KEY_BABY_ID = ?", arrayOf(babyId))
        }

        // 根据用户ID查询用户信息
        fun queryBabyById(babyId: String, db: SQLiteDatabase): UserEntity {
            val sql = "SELECT * FROM $USER_TABLE_NAME WHERE $KEY_BABY_ID = ?"
            val params = arrayOf(babyId)
            Log.d("queryUserById", "sql: $sql, params: ${params.joinToString()}")
            val cursor = db.rawQuery(sql, params)
            val userList = mutableListOf<UserEntity>()
            while (cursor.moveToNext()) {
                userList.add(cursorToUserEntity(cursor))
            }
            cursor.close()
            return userList.getOrElse(0) { UserEntity("1", "1", 0, 0, "1", "1", "1234") }
        }

        // 将 Cursor 转换为 UserEntity
        private fun cursorToUserEntity(cursor: Cursor): UserEntity {
            return UserEntity(
                cursor.getString(cursor.getColumnIndex(KEY_USER_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_BABY_ID)),
                cursor.getInt(cursor.getColumnIndex(KEY_CURRENT_TOTAL_SCORE)),
                cursor.getInt(cursor.getColumnIndex(KEY_CURRENT_RECEIVED_SCORE)),
                cursor.getString(cursor.getColumnIndex(KEY_SELECTED_TREE_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_SELECTED_TREE_SKIN_URL)),
                if (cursor.getColumnIndex(KEY_BABY_PWD) == -1) {
                    "1234"
                } else {
                    cursor.getString(cursor.getColumnIndex(KEY_BABY_PWD))
                },
            )
        }
    }
}
